package action;

import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.upload.FormFile;

import service.service_admin.Service_Admin;
import service.service_public.Service_Public;
import service.service_search.Service_Search;

import entitybean.l_sequence.EntityBeanL_Sequence;
import entitybean.oc_boat_ben.EntityBeanOc_Boat_Ben;
import entitybean.oc_date_ben.EnitityBeanOc_Date_Ben;
import entitybean.oc_depthlist_ben.EntityBeanOc_Depthlist_Ben;
import entitybean.oc_property_ben.EntityBean_Oc_Property_Ben;
import entitybean.oc_property_tools_ben.EntityBean_Oc_Property_Tools_Ben;
import entitybean.oc_station_ben.EntityBeanOc_Station_Ben;
import entitybean.oc_voyage_ben.EnitityBeanOc_Voyage_Ben;
import entitybean.oc_xls_upload.Oc_Xls_Upload;
import form.UpLoadForm;


public class UploadsXlsAction_Ben extends Action{
	private Service_Search service_search;
	private Service_Public service_public;
	private Service_Admin service_admin;
	
	
	
	public Service_Search getService_search() {
		return service_search;
	}



	public void setService_search(Service_Search serviceSearch) {
		service_search = serviceSearch;
	}



	public Service_Public getService_public() {
		return service_public;
	}



	public void setService_public(Service_Public servicePublic) {
		service_public = servicePublic;
	}



	public Service_Admin getService_admin() {
		return service_admin;
	}



	public void setService_admin(Service_Admin serviceAdmin) {
		service_admin = serviceAdmin;
	}



	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		String key="";
		String filename="";
		
		if (form instanceof UpLoadForm) {// 如果form是uploadsForm
			String encoding = request.getCharacterEncoding();
			if ((encoding != null) && (encoding.equalsIgnoreCase("utf-8"))) {
				response.setContentType("text/html; charset=gb2312");// 如果没有指定编码，编码格式为gb2312
			}
			UpLoadForm theForm = (UpLoadForm) form;
			FormFile file = theForm.getTheFile();// 取得上传的文件
			String boat_id=theForm.getId();
			System.out.println("boat_id="+boat_id);
			try {
				InputStream stream = file.getInputStream();// 把文件读入
				String filePath = request.getRealPath("/") + "upload";// 取当前系统路径

				ByteArrayOutputStream baos = new ByteArrayOutputStream();
				String time_key=this.MakeTimeString();
				String new_filename=file.getFileName();
				new_filename=new_filename.substring(0,new_filename.lastIndexOf("."));
				System.out.println("new_filename="+new_filename);
				new_filename=new_filename+time_key+".xls";
				OutputStream bos = new FileOutputStream(filePath + "/"
						+ new_filename);// 建立一个上传文件的输出流
				
				int bytesRead = 0;
				byte[] buffer = new byte[512];
				while ((bytesRead = stream.read(buffer, 0, 512)) != -1) {
					bos.write(buffer, 0, bytesRead);// 将文件写入服务器
				}
				bos.close();
				stream.close();
				System.out.println("文件所在地址=" + filePath + "\\"
						+ new_filename);
				 //key=this.ReadExcel_IMPORTUIM(filePath + "/" + file.getFileName());
			     filename=filePath + "\\" + new_filename;	 
			     Oc_Xls_Upload e=new Oc_Xls_Upload();
			     e.setDate_num("底栖环境");
			     e.setFile_name(new_filename);
			     e.setFile_path(filename);
			     EntityBeanL_Sequence l=this.service_public.Find_L_Sequence_By_seq_Name("SEQ_OC_XLS_UPLOAD");
			     Integer id=l.getSeq_Value()+1;
			     l.setSeq_Value(id);
			     e.setId(id);
			     e.setUpdate_time(new Date());
			     
			     
			     System.out.println("开始读取数据，导入工序");
			     
			   //开始读取数据，导入工序
			   //读取所有的站点，放入内存。
			   //读取所有的属性，放入内存。
			   //开始读取行。
			   //分析行的前几列，并在站点表中比对，如果存在则通过 不存在则放弃。
				//
			     EnitityBeanOc_Voyage_Ben enititybeanoc_voyage_ben=new EnitityBeanOc_Voyage_Ben();//校验用实例名
			     EntityBeanOc_Depthlist_Ben oc_depthlist_ben=new EntityBeanOc_Depthlist_Ben();//校验用实例名
			     EntityBeanOc_Station_Ben oc_station_ben=new EntityBeanOc_Station_Ben();//校验用实例名
			     EntityBean_Oc_Property_Tools_Ben oc_property_tools_ben=new  EntityBean_Oc_Property_Tools_Ben();//检查时的赋值对象 
			     EntityBean_Oc_Property_Ben oc_property_ben=new EntityBean_Oc_Property_Ben();//检查时的赋值对象
			     Double valuekey;//检查时的赋值对象
			    List<EnitityBeanOc_Voyage_Ben>voyagelist_ben=this.service_admin.Find_Oc_Voyage_Ben_By_boatid(Integer.valueOf(boat_id));//按照科考船读取数据
			   
			    System.out.println("根据科考船的数据获得了航次的大小="+voyagelist_ben.size());
			    List<EntityBeanOc_Depthlist_Ben>depthlist_ben=this.service_admin.Find_Oc_Depthlist_Ben();
			    
			    System.out.println("根据科考船的数据获得了层深的大小="+depthlist_ben.size());
			    List<EntityBeanOc_Station_Ben>stationlist_ben=new ArrayList<EntityBeanOc_Station_Ben>();
			   
			    stationlist_ben=this.service_admin.Find_Oc_Station_Ben_is_active();//获取了所有的有效站点
			    System.out.println("获取了所有的有效站点的大小="+stationlist_ben.size());
			    
			    List<EntityBean_Oc_Property_Ben>propertylist_ben= this.service_admin.Find_EntityBean_Oc_Property_Ben_by_property_kind(8);
			    List<String>propertylist_ben_str=new ArrayList<String>();
			    List<EntityBean_Oc_Property_Ben>propertylist_table_ben= new ArrayList<EntityBean_Oc_Property_Ben>();//记录在本表中使用到的属性值
			    for(int i=0;i<propertylist_ben.size();i++)
			    {
			    	propertylist_ben_str.add(propertylist_ben.get(i).getProperty_name());
			    	System.out.println(propertylist_ben.get(i).getProperty_name());
			    }
			    
			    
			    System.out.println("根据科考船的数据获得了属性的大小="+propertylist_ben.size());
			    List<EntityBean_Oc_Property_Tools_Ben>propertytoolslist_ben= this.service_admin.Find_EntityBean_Oc_Property_Tools_Ben_Is_Active();
			  
			    /*List<String>propertytoolsliststr=new ArrayList<String>();
			    for(int i=0;i<propertytoolslist.size();i++)
			    {
			    	propertytoolsliststr.add(propertytoolslist.get(i).getTools_name());
			    	System.out.println(propertytoolslist.get(i).getTools_name());
			    }*/
			    System.out.println("根据科考船的数据获得了属性工具的大小="+propertytoolslist_ben.size());
			    Workbook workBook = null;
			    InputStream is = new FileInputStream(filename);//读取上传的xls文件
			    workBook = Workbook.getWorkbook(is);
				// 从0开始，第一张工作表
				Sheet sheet = workBook.getSheet(0);
				int row = sheet.getRows();
				int column = sheet.getColumns();
				if(column<7)
				{
					System.out.println("表格宽度有问题,怀疑文件格式错误");
					request.setAttribute("error_reason","表格宽度有问题,怀疑文件格式错误");
					return mapping.findForward("error");
				}
				System.out.println("row=" + row);
				System.out.println("column=" + column);
				System.out.println("检查表头宽度");
				//航次	站位	  经度(E)	纬度(N)	采样时间(年-月-日-时:分)	深度（m）	层（m）	压强(db)	温度(deg C)	盐度(PSU)	
				//潜在温度(deg C)	潜在密度(Kg/m^3)	溶解氧(μM)	硝酸盐(μM)	磷酸盐(μM)	硅酸盐(μM)	
				//氨盐(μM)	亚硝酸盐(μM)	初级生产力(mgC﹒m-3h-1)	δ18O(‰)	备注
				List<String>liststr=new ArrayList<String>();
				liststr.add("航次");
				liststr.add("站位");
				liststr.add("经度(E)");
				liststr.add("纬度(N)");
				liststr.add("采样时间(年-月-日-时:分)");
				liststr.add("深度(m)");
				liststr.add("层(m)");
				for(int i=0;i<7;i++)
				{
					Cell cell = sheet.getCell(i, 0);
					if (!cell.getContents().toString().equals(liststr.get(i)))
					{
						System.out.println("表头数据有错误,期待的值="+liststr.get(i));
						request.setAttribute("error_reason","表头数据有错误,期待的值="+liststr.get(i));
						return mapping.findForward("error");
					}
				}
				for(int i=7;i<column-1;i++)
				{
					Cell cell = sheet.getCell(i, 0);
					String cellstr=cell.getContents().toString();
					Integer kh=cellstr.indexOf("(");
					cellstr=cellstr.substring(0,kh);
					System.out.println("cellstr="+cellstr);
					if(!propertylist_ben_str.contains(cellstr))
					{
						request.setAttribute("error_reason","系统中没有这个有效参数,查看有无 "+cellstr+" 属性");
						System.out.println("系统中没有这个有效参数,查看有无 "+cellstr+" 属性");
						return mapping.findForward("error");
					}
					else
					{
						//在propertylist_table增加相应的数据
						EntityBean_Oc_Property_Ben property_table_ben=new  EntityBean_Oc_Property_Ben();
						for(int ck_cellstr=0;ck_cellstr<propertylist_ben.size();ck_cellstr++)
						{
							if(cellstr.equals(propertylist_ben.get(ck_cellstr).getProperty_name()))
							{
								property_table_ben=propertylist_ben.get(ck_cellstr);
								propertylist_table_ben.add(property_table_ben);
							}
						}
					}
				}
				System.out.println("在导入的XLS文件中,使用的属性大小="+propertylist_table_ben.size());
				Cell cell = sheet.getCell(column-1, 0);
				String cellstr=cell.getContents().toString();
			
				if(!cellstr.equals("备注"))
				{
					System.out.println("最后一列不是备注列,期待的值=备注");
					request.setAttribute("error_reason","最后一列不是备注列,期待的值=备注");
					return mapping.findForward("error");
				}
				System.out.println("表格头部校验完成,开始校验表格数据");
				//忽略了英文的标题，从第三列开始读取数据
				List<EnitityBeanOc_Date_Ben>Date_list=new ArrayList<EnitityBeanOc_Date_Ben>();//生成一个需要最后写入到数据库的字段
				for(int i=2;i<row;i++)
				{
					EnitityBeanOc_Date_Ben e0=new EnitityBeanOc_Date_Ben();
					//校验航次
					cell = sheet.getCell(0, i);
					String voyagename=cell.getContents().toString();
					String find_voyage="not";
					for(int voyage_id=0;voyage_id<voyagelist_ben.size();voyage_id++)
					{
						if(voyagelist_ben.get(voyage_id).getVoyage_name().equals(voyagename))
						{
							enititybeanoc_voyage_ben=voyagelist_ben.get(voyage_id);
							find_voyage="yes";
							break;
						}
					}
					if(find_voyage.equals("yes"))
					{
						System.out.println("航次校验通过,航次名称="+enititybeanoc_voyage_ben.getVoyage_name());
						e0.setOc_voyage_id(enititybeanoc_voyage_ben.getId());//设置了一个航次数据
					}
					else
					{
						System.out.println("未能发现正确的航次名称,期待的值="+voyagename);
						request.setAttribute("error_reason","未能发现正确的航次名称,期待的值="+voyagename);
						return mapping.findForward("error");
					}
					//航次校验完成，开始校验站点
					EntityBeanOc_Station_Ben e_station=new EntityBeanOc_Station_Ben();
					cell = sheet.getCell(1, i);
					e_station.setStation_name(cell.getContents());
					cell = sheet.getCell(2, i);
					e_station.setLongitude(cell.getContents());
					cell = sheet.getCell(3, i);
					e_station.setDimensionality(cell.getContents());
					cell = sheet.getCell(5, i);
					e_station.setDepth(Double.valueOf(cell.getContents()));
					String find_station="not";
					for(int find_station_id=0;find_station_id<stationlist_ben.size();find_station_id++)
					{
						/*System.out.println("站点名称="+stationlist.get(find_station_id).getStation_name());
						System.out.println("站点深度="+stationlist.get(find_station_id).getDepth());
						System.out.println("站点经度="+stationlist.get(find_station_id).getDimensionality());
						System.out.println("站点维度="+stationlist.get(find_station_id).getLongitude());
						
						System.out.println("比较站点名称="+e_station.getStation_name());
						System.out.println("比较站点深度="+e_station.getDepth());
						System.out.println("比较站点经度="+e_station.getDimensionality());
						System.out.println("比较站点维度="+e_station.getLongitude());
						*/
						if(stationlist_ben.get(find_station_id).getStation_name().equals(e_station.getStation_name())
								&&stationlist_ben.get(find_station_id).getDepth()==e_station.getDepth()
								&&stationlist_ben.get(find_station_id).getDimensionality().equals(e_station.getDimensionality())
								&&stationlist_ben.get(find_station_id).getLongitude().equals(e_station.getLongitude())
								&&stationlist_ben.get(find_station_id).getOc_voyage_id().equals(e0.getOc_voyage_id()))
						{
							find_station="yes";
							e_station=stationlist_ben.get(find_station_id);
							e0.setOc_station_id(e_station.getId());//设置了站点值
							break;
						}
					}
					if(find_station.equals("yes"))
					{
						System.out.println("站点校验通过,站点名称="+e_station.getStation_name());
					}
					else
					{
						System.out.println("未能发现正确的站点名称,期待的值="+e_station.getStation_name());
						request.setAttribute("error_reason","未能发现正确的站点名称,期待的值="+e_station.getStation_name());
						return mapping.findForward("error");
					}//站点校验完成
					cell = sheet.getCell(6, i);
					EntityBeanOc_Depthlist_Ben entityBeanoc_depthlist_ben=new EntityBeanOc_Depthlist_Ben();
					entityBeanoc_depthlist_ben.setDepth(Integer.valueOf(cell.getContents()));
					String find_depthlist="not";
					for(int deplist_id=0;deplist_id<depthlist_ben.size();deplist_id++)
					{
						if(entityBeanoc_depthlist_ben.getDepth().equals(depthlist_ben.get(deplist_id).getDepth()))
						{
							find_depthlist="yes";
							entityBeanoc_depthlist_ben=depthlist_ben.get(deplist_id);
							break;
						}
					}
					if(find_depthlist.equals("yes"))
					{
						System.out.println("层深 校验通过,层深 名称="+entityBeanoc_depthlist_ben.getDepth());
						e0.setOc_deplist_id(entityBeanoc_depthlist_ben.getId());//设置了一个深度数据
					}
					else
					{
						System.out.println("层深 校验通过,层深 名称="+entityBeanoc_depthlist_ben.getDepth());
						request.setAttribute("error_reason","层深 校验通过,层深 名称="+entityBeanoc_depthlist_ben.getDepth());
						return mapping.findForward("error");
					}//层深校验完成
					
					
					for(int property_id=7;property_id<column-1;property_id++)
					{
						cell = sheet.getCell(property_id, i);
					    String cellproperty=cell.getContents();
						Integer kh_1=cellproperty.indexOf("(");
						Integer kh_2=cellproperty.indexOf(")");
						if(kh_1==-1||kh_2==-1)
						{
							System.out.println("属性值中缺少工具内容");
							request.setAttribute("error_reason","属性值中缺少工具内容");
							return mapping.findForward("error");
						}
						valuekey=Double.valueOf(cellproperty.substring(0,kh_1));
						String property_tool=cellproperty.substring(kh_1+1,kh_2);
						//System.out.println("属性的值="+valuekey);
						//System.out.println("属性工具名称="+property_tool);
						oc_property_ben=propertylist_table_ben.get(property_id-7);
						Integer pid=propertylist_table_ben.get(property_id-7).getId();
						//System.out.println("属性的PID="+pid);
						String find_property_tools="not";
						for(int ch_property_tool_id=0;ch_property_tool_id<propertytoolslist_ben.size();ch_property_tool_id++)
						{
							//System.out.println(propertytoolslist.get(ch_property_tool_id).getTools_name());
							//System.out.println(propertytoolslist.get(ch_property_tool_id).getPid());
							if(propertytoolslist_ben.get(ch_property_tool_id).getPid().equals(pid)
									&&propertytoolslist_ben.get(ch_property_tool_id).getTools_name().equals(property_tool))
							{
								 find_property_tools="yes";					
								 oc_property_tools_ben=propertytoolslist_ben.get(ch_property_tool_id);		
								 break;
							}
						}
						if(find_property_tools.equals("yes"))
						{
							 EnitityBeanOc_Date_Ben Oc_Date_Ben=new EnitityBeanOc_Date_Ben();
							 Oc_Date_Ben.setDel("OFF");
							 Oc_Date_Ben.setOc_deplist_id(e0.getOc_deplist_id());
							 Oc_Date_Ben.setOc_voyage_id(e0.getOc_voyage_id());
							 Oc_Date_Ben.setOc_station_id(e0.getOc_station_id());
							 Oc_Date_Ben.setOc_property_id(oc_property_ben.getId());
							 Oc_Date_Ben.setOc_property_tools_id(oc_property_tools_ben.getId());
							 Oc_Date_Ben.setValue_key(valuekey);
							 Oc_Date_Ben.setOc_xls_upload(0);
							 System.out.println("为录入的表单增加了一个数据"+Oc_Date_Ben.Read_Me());
							 Date_list.add(Oc_Date_Ben);		
						}
						else
						{
							System.out.println("未能发现合适的属性测量工具,期待的值="+property_tool);
							request.setAttribute("error_reason","未能发现合适的属性测量工具,期待的值="+property_tool);
							return mapping.findForward("error");
						}
						
						
					}
					
					
					
					
					
					
					
					
					
					//按照条件搜索站点名称 找到为有效 没找到就是无效

				}
				
				
				
				
				
				
				
				
				System.out.println("数据分析结束,开始记录到数据库中...");
				
				System.out.println("需要录入的数据为="+Date_list.size());
				EntityBeanL_Sequence L_DATE=this.service_public.Find_L_Sequence_By_seq_Name("SEQ_OC_DATE_BEN");
				Integer id_date=L_DATE.getSeq_Value()+1;
				for(int install=0;install<Date_list.size();install++)
				{
					Date_list.get(install).setOc_xls_upload(e.getId());
					Date_list.get(install).setId(id_date);
					
					id_date=id_date+1;
					this.service_admin.Insert_Oc_Data_Ben(Date_list.get(install));
				}
				L_DATE.setSeq_Value(id_date);
				e.setBoat_id(Integer.valueOf(boat_id));
				this.service_public.Update_L_Sequence(L_DATE);				
				this.service_admin.Insert_Oc_Xls_Upload(e);
				this.service_public.Update_L_Sequence(l);
				List<EntityBeanOc_Boat_Ben>list=this.service_admin.Find_Oc_Boat_Ben_By_oc_boatkind_Active();
				request.setAttribute("list",list);
			} catch (Exception e) {
				System.err.print(e);
				System.out.println("xls格式出错");
				request.setAttribute("error_reason","xls格式出错");
				return mapping.findForward("error");
			}
			request.setAttribute("key", key);
			request.setAttribute("filename", filename);
			request.setAttribute("ok", "数据上传完成");
			return mapping.findForward("success");
		}
		request.setAttribute("error_reason","未知的错误!");
		return mapping.findForward("error");
	}

	
	
	
	
	private String MakeTimeString() {
		//String year=d.getYear();
		Calendar c=Calendar.getInstance();
		String year=String.valueOf(c.get(Calendar.YEAR));
		String month=String.valueOf(c.get(Calendar.MONTH)+1);
		String day=String.valueOf(c.get(Calendar.DATE));
		String hour=String.valueOf(c.get(Calendar.HOUR_OF_DAY));
		if(hour.length()<2)
		{
			hour="0"+hour;		
		}
		String min=String.valueOf(c.get(Calendar.MINUTE));
		if(min.length()<2)
		{
			min="0"+min;		
		}
		String second=String.valueOf(c.get(Calendar.SECOND));
		if(second.length()<2)
		{
			second="0"+second;		
		}
		String str=year+"_"+month+"_"+day+"_"+hour+"_"+min+"_"+second;
		 System.out.println(str);
		return str;
	}
}
